<?php
/**************************
 * Created on Jan 22, 2011
 * By: Jos Pape
 * info: jospape@hotmail.com
 */
include_once "../../../common.php";
include_once $root."/header.php";

/**********
 * functions
 */
function addNewWebshop($naam='', $url='', $email='', $opmerking,$voornaam='', $achternaam='', $voorletters='', $tussenvoegsel='', $mobiel='')
{
	global $_MySQL;
	// check data
	if($naam == '' || $naam == 'webshopnaam')
		$error['Naam'] = 'Webshopnaam mag niet leeg zijn';
		
	if($url == '' || $url == 'URL')
		$error['URL'] = 'URL mag niet leeg zijn';
	
	if(!isset($error))
	{
		// check if exists
		$query = "SELECT ID FROM Webshops WHERE Name = '".str_replace("'","&#39;",trim($naam))."' OR URL = '".str_replace("'","&#39;",createCleanURL(trim($url)))."'";
		$result = $_MySQL->runQuery($query);
		if(mysql_num_rows($result) == 0)
		{
			//insert
			$query = "INSERT INTO Webshops (
							Name,			 	 	 	 	 	 	 
							URL,			 	 	 				 
							CreateDate,			 	 	 	 	 	 	
							UserID,			 	 	 	 	 	 	
							Status,
							Opmerking
						) VALUES (
							'".str_replace("'","&#39;",trim($naam))."',			 	 	 	 	 	 	 
							'".str_replace("'","&#39;",createCleanURL(trim($url)))."',			 	 	 				 
							NOW(),			 	 	 	 	 	 	
							1,			 	 	 	 	 	 	
							1,
							'".str_replace("'","&#39;",trim($opmerking))."'
						)";
			$_MySQL->runQuery($query);
			$webshopID = $_MySQL->lastInsertID();
			
			$query = "INSERT INTO WebshopContactpersonen (
							WebshopID,			 	 	 	 	 	 	
							Firstname,			 	 	 	 	 	 	 
							Initials,			 	 	 	 	 	 	 
							Surname,			 	 	 	 	 	 	 
							Insertion,			 	 	 	 	 	 	 
							Email,		 	 	 				 
							Mobile,		 	 	 	 	 	 	 
							CreateDate
						) VALUES (
							'".str_replace("'","&#39;",$webshopID)."',			 	 	 	 	 	 	
							'".str_replace("'","&#39;",$voornaam)."',	 	 	 	 	 	 	 
							'".str_replace("'","&#39;",$voorletters)."',			 	 	 	 	 	 	 
							'".str_replace("'","&#39;",$achternaam)."',	 	 	 	 	 	 	 
							'".str_replace("'","&#39;",$tussenvoegsel)."',		 	 	 	 	 	 	 
							'".str_replace("'","&#39;",$email)."',	 				 
							'".str_replace("'","&#39;",$mobiel)."', 	 	 	 	 	 	 
							NOW()
						)";
			$_MySQL->runQuery($query);
			echo $naam." toegevoegd (".$webshopID.")<br />";
		}
		else
		{
			echo "<br />".$naam." Bestaat al in de database<br />";
		}
	}
	else
	{
		// ERRORS
		?>
		<ul class="error">
		<?php
		foreach($error AS $what => $why)
		{
			?>
			<li><b><?=$what;?></b>: <?=$why;?></li>	
			<?php	
		}
		?>
		</ul>
		<?php
	}
}
/***********
 * POST
 */
if(isset($_POST['action']) && $_POST['action'] == "1voor1")
{
	addNewWebshop(
		$_POST['Name'], 
		$_POST['URL'], 
		$_POST['Email'], 
		'',
		$_POST['Firstname'], 
		$_POST['Surname'], 
		$_POST['Initials'], 
		$_POST['Insertion'], 
		$_POST['Mobile']
	);
}
elseif(isset($_POST['action']) && $_POST['action'] == "batch")
{
	// inleze Ecel file
	// ExcelFile($filename, $encoding);
	$data = new Spreadsheet_Excel_Reader();
	// Set output Encoding.
	$data->setOutputEncoding('CP1251');
	$data->read($_FILES['userfile']['tmp_name']);
	
	for ($i = 2; $i <= ($data->sheets[0]['numRows']-1); $i++) 
	{
		//$data->sheets[0]['cells'][$i][1]; // Webshopnaam
		//$data->sheets[0]['cells'][$i][2]; //URL
		//$data->sheets[0]['cells'][$i][3]; //Email
		//$data->sheets[0]['cells'][$i][4]; //Contactnaam
		//$data->sheets[0]['cells'][$i][5]; //opmerkingen
		addNewWebshop(
			$data->sheets[0]['cells'][$i][1], 
			$data->sheets[0]['cells'][$i][2], 
			$data->sheets[0]['cells'][$i][3],
			$data->sheets[0]['cells'][$i][5], 
			'', 
			$data->sheets[0]['cells'][$i][4]
		);
	}
}
/************
 * Display
 */
?>
<h1>Potenciele klanten invoeren</h1>
<form action="?" method="post">
	<table>
		<tr>
			<td>
				Naam
			</td>
			<td>:</td>
			<td>
				<input type="text" name="Name" />
			</td>
		</tr>
		<tr>
			<td>
				URL
			</td>
			<td>:</td>
			<td>
				<input type="text" name="URL" />
			</td>
		</tr>
		<tr>
			<td colspan="3">
				<u>Contactpersoon</u>
			</td>
		</tr>
		<tr>
			<td>
				Voornaam
			</td>
			<td>:</td>
			<td>
				<input type="text" name="Firstname" />
			</td>
		</tr>	
		<tr>
			<td>
				Voorletters
			</td>
			<td>:</td>
			<td>
				<input type="text" name="Initials" />
			</td>
		</tr>			 	 		
		<tr>
			<td>
				Achternaam
			</td>
			<td>:</td>
			<td>
				<input type="text" name="Surname" />
			</td>
		</tr>		 	 	 	 	 	 	 
		<tr>
			<td>
				Tussenvoegsel
			</td>
			<td>:</td>
			<td>
				<input type="text" name="Insertion" />
			</td>
		</tr>			 	 	 	 	 	 	 	
		<tr>
			<td>
				Email
			</td>
			<td>:</td>
			<td>
				<input type="text" name="Email" />
			</td>
		</tr>		 	 	 	 	 	 	 
		<tr>
			<td>
				Mobiel
			</td>
			<td>:</td>
			<td>
				<input type="text" name="Mobile" />
			</td>
		</tr>		 
		<tr>
			<td colspan="3">
				<input type="submit" value=" Toevoegen " />
				<input type="hidden" name="action" value="1voor1" />				 
			</td> 
		</tr>
	</table>
</form>
<br />
<h2>Batch verwerking</h2>
<form action="?" method="post" enctype="multipart/form-data">
<table>	 	 	 	 	 	 
	<tr>
		<td>
			Bestand
		</td>
		<td>:</td>
		<td>
			<input type="file" name="userfile" />
		</td>
	</tr>		
	<tr>
		<td colspan="3">
			<input type="submit" value=" Upload " />
			<input type="hidden" name="action" value="batch" />
		</td>
	</tr>
</table>
</form>
<a href="http://www.prijsgezocht.nl/database/page/klanten/potencieel/emptysheet.php">Lege Excel sheet downloaden</a>
<?php
include_once $root."/footer.php";
